PostgresqlのFDWで異なるデータベース間でSQLを実行する方法 (docker SQLServer連携)

---

公開: 2022年07月16日

PostgreSQL FDW 外部DB 連携

目次

FDW（Foreign Data Wrapper）とは

オープンソースソフトウェア(OSS)としてPostgreSQLがありますが、このPostgreSQLにFDWという機能があります。
FDWは外部DB連携ができる機能で、データベースに無いテーブルであっても外部データベースに問い合わせて、同じデータベースにあるように動作します。
なので、アプリケーション側は複数のデータベースがあることを意識せずに、一つのSQLクエリを発行するだけで済むので、開発が楽になります。

例えば以下の図のように、Web Server1がWebアプリケーションとしてDB接続していますが、 SQLで取得したいデータがMicrosoft SQL Serverだったり、Web Server2のDBであるPostgreSQL2にあったりします。

PostgreSQL FDW 外部DB 連携の例

社内に複数のWebシステムがあって、人事情報を管理するシステムや勤休を管理するシステムがあったりします。
これらの情報を統合してデータ分析をしたい場合にそれぞれのDBにアクセスしてアプリ側で結合しないといけませんが、 FDWを使うことでアプリケーション側は一つのSQLで関連データをJOINして取得できます。

Microsoft SQL Serverなら tds_fdwを使う

Github: tds_fdw

tds_fdwはMicrosoft SQL Server向けのPostgresqlのFDWです。 サポートするPostgresqlのバージョンはPostgreSQL 9.2+です。

しかし私がPostgreSQL12や14で試したときに、うまくデータ取得ができないバグがあって、 結局PostgreSQL11.4を使ったら安定して動作しました。

DockerでPostgresqlにtds_fdwを使う

DockerでPostgresqlにtds_fdwを使う方法を紹介します。

以下DockerFileです。

FROM postgres:11.4

RUN apt-get update
RUN apt-get -y install libsybdb5 freetds-dev freetds-common libpq-dev git make gcc curl ca-certificates 

RUN curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
RUN sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

RUN apt-get update
RUN apt-get -y install postgresql-server-dev-11

RUN git clone -q https://github.com/tds-fdw/tds_fdw.git tds_fdw
WORKDIR tds_fdw
RUN make USE_PGXS=1
RUN make USE_PGXS=1 install

RUN apt-get -y remove git make curl gcc

FROM postgres:11.4

postgres:11.4をベースコンテナにします。

RUN git clone -q https://github.com/tds-fdw/tds_fdw.git tds_fdw

特記する場所としては上記で、tds_fdwのgithubからソースをcloneします。
そのあとmakeでビルドしてpostgresqlで使えるようになります。

PostgreSQLにFDWの仮想テーブルを作成

tds_fdwが使用できるようになったら、実際にpostgreSQL上にFDW先の仮想テーブルを作ります。

今回はdockerでpostgresqlを動かしているので、docker execコマンド経由でpsqlコマンドを実行します。

# psql for postgresql container
docker exec -it docker-compose_db1_1 psql -d postgres -U postgres -c "CREATE EXTENSION tds_fdw;"

docker exec -it docker-compose_db1_1 psql -d postgres -U postgres -c "create server foreign_server foreign data wrapper tds_fdw options (servername '10.0.2.2', port '1433', database 'Test', tds_version '7.2', character_set 'UTF-8');"

docker exec -it docker-compose_db1_1 psql -d postgres -U postgres -c "create user mapping for postgres server foreign_server options (username 'sa', password 'YOUR_PASSWORD');"

docker exec -it docker-compose_db1_1 psql -d postgres -U postgres -c "grant all on foreign server foreign_server to postgres;"

docker exec -it docker-compose_db1_1 psql -d postgres -U postgres -c "create foreign table app_reception (id integer, name VARCHAR(255)) server foreign_server options (table 'foreign_table_name');"

上記を作成するとforeign_table_nameの仮想テーブルがpostgresql上に作成できます。
あとはSELECT文等で実際にデータが取得出来ればOKです。